package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * The DatabaseHandler class is responsible for all interactions
 * between the application and the database.
 * Implements the Singleton design pattern, as there should never
 * be more than a single instance of the DatabaseHandler running.
 *
 * @author Robin Persson
 */

public class DatabaseHandler {
	private Integer nextId = null;
	
	private Double stepLength = null;
	private String address = "";

	private final int stepLengthId = 0;
	private final int addressId = 1;
	
    private static final String dbURL = "jdbc:mysql://db.student.chalmers.se:3306/perrob";
    private static final String user = "perrob";
    private static final String password = "MMhdzEnQ";
    
    private static DatabaseHandler instance;
    private Connection connection;
    private Statement stmt;
    private Logger logger = Logger.getLogger("database.DatabaseHandler");

    /**
     * Initiate a new instance of the DatabaseHandler.
     */
    protected DatabaseHandler() {
        try {
            //Register the JDBC driver for MySQL.
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            logger.log(Level.SEVERE, null, ex);
        }

        connectToDatabase();
        
		if(nextId == null) {
	        try {
	            stmt = connection.createStatement();
	            String sqlQuery = "SELECT id FROM RunSessionData ORDER BY id DESC LIMIT 1";
	            ResultSet rs = stmt.executeQuery(sqlQuery);
	            if (rs.next()) {
	                nextId = rs.getInt("id") + 1;
	            } else {
	            	nextId = 0;
	            }
	        } catch (SQLException ex) {
	            logger.log(Level.SEVERE, null, ex);
	        }
		}
    }

    /**
     * Return the instance of DatabaseHandler, if there is no present,
     * initiate a new one.
     * @return instance of DatabaseHandler
     */
    public synchronized static DatabaseHandler getInstance() {
        if (instance == null) {
            instance = new DatabaseHandler();
        }
        return instance;
    }

    /**
     * Creates the connection to the database.
     */
    public synchronized void connectToDatabase() {
        try {
            connection = DriverManager.getConnection(dbURL, user, password);

            logger.log(Level.INFO, "Connected to database.");
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }
    }
	
	public synchronized int newRunSession(ArrayList<Long> data) {
		if(nextId == null) {
			getNextId();
		}
		int id = nextId;
		nextId++;

		long timestamp;
		Iterator<Long> iterator = data.iterator();
		while(iterator.hasNext()) {
			timestamp = iterator.next(); //Timestamp in milliseconds.
	        try {
	            stmt = connection.createStatement();
	            String sqlValues = id + ", " + timestamp;
	            String insertSQL = "INSERT INTO RunSessionData(id, timestamp) VALUES(" + sqlValues + ")";
	            stmt.executeUpdate(insertSQL);
	            
	            logger.log(Level.INFO, "Inserted step in session: " + id + " @ " + new Timestamp(timestamp));
	        } catch (SQLException ex) {
	            logger.log(Level.SEVERE, null, ex);
	        }
		}
		return id;
	}
	
	public synchronized int getNextId() {
		if(nextId == null) {
	        try {
	            stmt = connection.createStatement();
	            String sqlQuery = "SELECT id FROM RunSessionData ORDER BY id DESC LIMIT 1";
	            ResultSet rs = stmt.executeQuery(sqlQuery);
	            if (rs.next()) {
	                nextId = rs.getInt("id");
	            } else {
	            	nextId = 0;
	            }
	        } catch (SQLException ex) {
	            logger.log(Level.SEVERE, null, ex);
	        }
		}
		return nextId;
	}
	
	public synchronized boolean isGoodPerformance(int threshold) {
		return (isGoodDurationPerformance(threshold) || isGoodDistancePerformance(threshold));
	}
	
	protected synchronized boolean isGoodDurationPerformance(int threshold) {
		boolean performance = false;

        String sqlQuery = 
			"SELECT " +
					"MAX(id) " +
						"AS id, " +
					"ROUND(AVG(duration)) " +
						"AS duration " +
				"FROM " +
				"(SELECT " +
						"id, " +
						"MAX(timestamp) - MIN(timestamp) " +
							"AS duration " +
					"FROM RunSessionData " +
					"WHERE id " +
						"BETWEEN " + (nextId - 7) + " AND " + (nextId - 2) + " " +
					"GROUP BY id) " +
				"AS avgRuns " +
			"UNION ALL " +
			"SELECT " +
					"id, " +
					"MAX(timestamp) - MIN(timestamp) " +
						"AS duration " +
				"FROM RunSessionData " +
				"WHERE id = " + (nextId - 1) + " " +
				"GROUP BY id " +
			"ORDER BY id ASC";

        long 	avgDuration = 0,
        		lastRunDuration = 0;
		
        try {
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sqlQuery);

            //printResults(rs);
            if(rs.next()) {
            	avgDuration = rs.getLong("duration");
            	if(rs.next()) {
                	lastRunDuration = rs.getLong("duration");
            	}
            }

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }
        
		if(avgDuration > 0 && lastRunDuration > 0) {
			if((avgDuration * threshold) < (lastRunDuration * 100)) {
				performance = true;
			}
		}
		
		if(performance) {
			logger.log(Level.INFO, "Good duration performance!");
		}
		
		return performance;
	}
	
	protected synchronized boolean isGoodDistancePerformance(int threshold) {
		boolean performance = false;

		if(stepLength == null) {
			try {
				getStepLength();
			} catch(NullPointerException ex) {}
		}

		if(stepLength != null) {
	        String sqlQuery = 
				"SELECT " +
						"MAX(id) " +
							"AS id, " +
						"ROUND(AVG(nSteps)) " +
							"AS nSteps " +
					"FROM " +
					"(SELECT " +
							"id, " +
							"COUNT(timestamp) " +
								"AS nSteps " +
						"FROM RunSessionData " +
						"WHERE id " +
							"BETWEEN " + (nextId - 7) + " AND " + (nextId - 2) + " " +
						"GROUP BY id) " +
					"AS avgRuns " +
				"UNION ALL " +
				"SELECT " +
						"id, " +
						"COUNT(timestamp) " +
							"AS nSteps " +
					"FROM RunSessionData " +
					"WHERE id = " + (nextId - 1) + " " +
					"GROUP BY id " +
				"ORDER BY id ASC";
			
	        int		avgNSteps = 0,
	        		lastRunNSteps = 0;
	        double 	avgDistance = 0,
	        		lastRunDistance = 0;
	        
	        try {
	            stmt = connection.createStatement();
	            ResultSet rs = stmt.executeQuery(sqlQuery);
	            
	            //printResults(rs);
	
	            if(rs.next()) {
	            	avgNSteps = rs.getInt("nSteps");
	            	avgDistance = avgNSteps * stepLength;
	            	if(rs.next()) {
	                	lastRunNSteps = rs.getInt("nSteps");
	                	lastRunDistance = lastRunNSteps * stepLength;
	            	}
	            }
	
	        } catch (SQLException ex) {
	            logger.log(Level.SEVERE, null, ex);
	        }
	        
			if(avgDistance > 0 && lastRunDistance > 0) {
				if((avgDistance * threshold) < (lastRunDistance * 100)) {
					performance = true;
				}
			}
		}
		
		if(performance) {
			logger.log(Level.INFO, "Good distance performance!");
		}
		return performance;
	}
	
	/**
	 * For debugging. Prints all data in the resultset.
	 * @param rs The resultset to print
	 */
	private synchronized void printResults(ResultSet rs) {
        try {
			ResultSetMetaData md = rs.getMetaData();
	        int count = md.getColumnCount();
	        for (int i=1; i<=count; i++) {
	        	System.out.print(md.getColumnLabel(i) + "\t");
	        }
	        System.out.println("");
	        while (rs.next()) {
	          for (int i=1; i<=count; i++) {
	        	  System.out.print(rs.getString(i) + "\t");
	          }
	          System.out.println("");
	        }
        } catch(SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }
	}
	
    public synchronized String getData() {
        String sqlQuery = "SELECT id, MIN(timestamp) AS timestamp FROM RunSessionData " +
			"GROUP BY id";
        int id = 0;
        Timestamp timestamp = Timestamp.valueOf("0000-00-00 00:00:00.000");
        String result = "";

        try {
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sqlQuery);

            while(rs.next()) {
                id = rs.getInt("id");
                timestamp = new Timestamp(rs.getLong("timestamp"));
                result += id + " @ " + timestamp +"\n";
            }

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }

        return result;
    }

    /**
     * Gives the timestamp of the specified run session
     * @param id The id of the run session
     * @return The timestamp of the run session,
     *         "0000-00-00 00:00:00" if not available.
     */
    public synchronized long getLastRunTimestamp() {
		long timestamp = 0;

        String sqlQuery = "SELECT id, MIN(timestamp) AS timestamp FROM RunSessionData " +
				"GROUP BY id " +
				"ORDER BY id ASC LIMIT 1";

        try {
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sqlQuery);

            if (rs.next()) {
                timestamp = rs.getLong("timestamp");
            }

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }

        return timestamp;
    }
    
    protected synchronized double getStepLength() {
        String sqlQuery = 
    		"SELECT floater " +
    			"FROM SystemVariables " +
        		"WHERE id=" + stepLengthId;

        try {
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sqlQuery);

            if (rs.next()) {
                stepLength = (rs.getDouble("floater"));
            }

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }
        return stepLength;
    }
    
    public synchronized String getAddress() {
    	String address = "";
        String sqlQuery = 
    		"SELECT string " +
    			"FROM SystemVariables " +
        		"WHERE id=" + addressId;

        try {
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sqlQuery);

            if (rs.next()) {
                address = (rs.getString("string"));
            }
            logger.log(Level.INFO, "Address fetched: " + address);
            this.address = address;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }
        return address;
    }
	
	public synchronized void setStepLength(double stepLength) {
        try {
            stmt = connection.createStatement();
            String updateSQL = 
            	"UPDATE SystemVariables " +
            		"SET floater =" + stepLength + " " +
    				"WHERE id =" + stepLengthId;
            stmt.executeUpdate(updateSQL);
            
            this.stepLength = stepLength;
            logger.log(Level.INFO, "Step length changed to: " + stepLength + "m.");
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }   
	}
	
	public synchronized void setAddress(String address) {
        try {
            stmt = connection.createStatement();
            String updateSQL = 
            	"UPDATE SystemVariables " +
            		"SET string = '" + address + "' " +
    				"WHERE id = " + addressId;
            stmt.executeUpdate(updateSQL);
            
            this.address = address;
            logger.log(Level.INFO, "Address changed to: " + address + ".");
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }   
	}
	
	public synchronized void clearTables() {
		try {
            stmt = connection.createStatement();
			stmt.executeUpdate("DROP TABLE RunSessionData");
            logger.log(Level.INFO, "Data table dropped.");
		} catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
		}
		try {
            stmt = connection.createStatement();
			stmt.executeUpdate("DROP TABLE SystemVariables");
            logger.log(Level.INFO, "System variables table dropped.");
		} catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
		}
	}
	
	public synchronized void createTables() {
		try {
            stmt = connection.createStatement();
			stmt.executeUpdate(
				"CREATE TABLE RunSessionData (id INT, timestamp BIGINT, PRIMARY KEY (id, timestamp))");
            logger.log(Level.INFO, "Data table created.");
		} catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
		}
		try {
            stmt = connection.createStatement();
			stmt.executeUpdate(
				"CREATE TABLE SystemVariables (id INT, floater FLOAT, string CHAR(255), PRIMARY KEY (id))");
            logger.log(Level.INFO, "System variables table created.");
		} catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
		}
		try {
            stmt = connection.createStatement();
			stmt.executeUpdate(
				"INSERT INTO SystemVariables(id, floater, string) " +
					"VALUES " +
						"("+ addressId +", NULL, ''), " +
						"("+ stepLengthId +", NULL, NULL)");
            logger.log(Level.INFO, "Initiated system variables.");
		} catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
		}
	}
}
